<html xmlns="http://www.w3.org/1999/xhtml">
<head>
  
  <title>Jailer - Deleting Data</title>
  <meta http-equiv="Content-Type" content="text/html; charset=us-ascii" />
  <meta name="description" content="Data Export Tool" />
  <meta name="keywords" content="data export referential integrity java jdbc dbms" />
  <link rel="stylesheet" type="text/css" href="styles.css" />
  <link rel="shortcut icon" href="favicon.ico" />
</head>

<body left-margin="0" top-margin="0">
  <div align="left">
    <table style="text-align: left; position: absolute; left: 0pt; top: 0pt;" border="0"
    cellpadding="0" cellspacing="0" height="407" width="100%">
      <tbody>
        <tr>
          <td colspan="2" class="bannerbackground" height="33" width="100%">
            <table border="0" cellpadding="4" cellspacing="0" width="100%">
              <tbody>
                <tr>
                  <td><a href="https://github.com/Wisser/Jailer"><img src="logo.png" hspace="10" /></td> <td class="slogan" width="100%"></a>

                   

                  <td style="text-align: right; width: 100%;">
                    <table border="0" cellpadding="0" cellspacing="0">
                      <tbody>
                        <tr>
                           <td><a href="https://github.com/Wisser/Jailer"><img src="GitHub-Mark-64px.png" title="GitHub" /></a> </td></tr><tr style="height: 100%;"><td>&nbsp;</td>
                        </tr>
                      </tbody>
                    </table>
                  </td>
                </tr>
              </tbody>
            </table>
          </td>
        </tr>

        <tr>
          <td colspan="2" class="nav1background"  width="100%">&nbsp;
          <b><font color="#FFFFFF"><a href="home.htm" target="_self" class=
          "tlink">&nbsp;Home&nbsp;</a> <a href="quicktour.htm" target="_self"
          class="tlink">&nbsp;Quick Tour&nbsp;</a> <a href="exporting-data.htm"
          target="_self" class="tlinkA">&nbsp;Tutorial&nbsp;</a> <a href=
          "data-browsing.html" target="_self" class="tlink">&nbsp;Data Browser&nbsp;</a> <a class="tlink" href="videos.html" target="_self">&nbsp;Videos&nbsp;</a> 
          <a href="faq.html" target="_self" class="tlink">&nbsp;FAQ&nbsp;</a> 
          <a href="api.html" target="_self" class="tlink">&nbsp;API&nbsp;</a> 
          <a href="design.htm" target="_self" class=
          "tlink">&nbsp;Documentation&nbsp;</a> <a href=
          "http://sourceforge.net/forum/?group_id=197260" target="_self" class=
          "tlink">&nbsp;Forum&nbsp;</a> <a href=
          "http://sourceforge.net/project/showfiles.php?group_id=197260" target="_self"
          class="tlink">&nbsp;Download&nbsp;</a>  &nbsp;&nbsp;&nbsp;
          &nbsp;&nbsp;&nbsp;</font></b></td>
        </tr>

        <tr>
          <td colspan="2" class="spacer" width="100%"></td> </tr><tr><td class="lmenucontainer">&nbsp;</td>
        </tr>

        <tr>
          <td class="lmenucontainer" height="100%" valign="top" width="14%">
            <div align="center">
              <center>
                <table border="0" cellpadding="0" cellspacing="0" width="100%">
                  <tbody>
                    <tr>
                      <td height="6px" width="100%"> </td>
                    </tr>
                  </tbody>
                </table>
              </center>
            </div>

            <ul>
              <li><a href="preparation.htm" target="_self" class="mlink">Preparation</a> </li><br>
              <li>Basic</li>

                <ul>
                  <li><a href="exporting-data.htm" target="_self" class="llink">Exporting Data</a> </li>
                </ul>

                <ul>
                  <li><a href="deleting-data.htm" target="_self" class="llinkA">Deleting
                  Data</a> </li>
                </ul>
<br>
              <li>Advanced</li>

                <ul>
                  <li><a href="filters.html" target="_self" class=
                  "llink">Filters</a> </li>
                </ul>

                <ul>
                  <li><a href="subset-by-example.html" target="_self" class=
                  "llink">Subset&nbsp;By&nbsp;Example</a> </li>
				  <li><a href="exporting-xml.html" target="_self" class="llink">JSON/YAML/XML Export</a> </li>
                </ul>
            </ul>

            <table border="0" cellpadding="5" cellspacing="0" width="100%">
              <tbody>
                <tr>
                  <td class="content2background"><img style="width: 160px; height: 1px;"
                  alt="" src="architecture-Dateien/vgradp.gif" /></td>
                </tr>
              </tbody>
            </table>
          </td>

          <td class="contentbackground" height="418" valign="top" width="86%">
            <div align="right">
              <table border="0" cellpadding="0" cellspacing="0" height="542" width="98%">
                <tbody>
                  <tr>
                    <td class="contentbackground" height="21" width="100%">&nbsp;</td>
                  </tr>

                  <tr>
                    <td class="content" height="520" valign="top" width="100%">
                      
                      This tutorial shows you how to delete rows from database without
                      violating integrity of the remaining data.<br />
                      <br />
                      Deleting data is closely coupled with data exportation. The same
                      restrictions are applied, no row will be deleted which is not also
                      exported. This way you can always restore deleted rows by
                      re-importing them.<br />
                      <br />
                      A <span style="font-style: italic;">delete-script</span> contains
                      DELETE statements for all exported rows which are not associated
                      with any non-exported row according to the restricted data model.
                      Additionally tables can be excluded from deletion so that their
                      rows will never be deleted (see step 2)<br />

                      <h4><br />
                      Objective</h4>Delete all projects which have ended before
                      2008-01-01. Delete all associated participations too but no role
                      and no employee.<br />
                      <br />

                      <h4>Step 1. Delete projects<br /></h4>Create a new model. Select
                      table PROJECT as subject table and enter T.END_DATE&lt;'2008-01-01'
                      as subject condition.<br />
                      <br />
                      In order to prevent the deletion of a PROJECT_PARTICIPATION, which
                      is not associated with an old project but with a role of a
                      participation in an old project, disable the association from ROLE
                      to PROJECT_PARTICIPATION. Do the same with the association from
                      EMPLOYEE to PROJECT_PARTICIPATION.<br />
                      <br />
                      Disable all associations to EMPLOYEE too. This will reduce the set
                      of exported employee-rows to those employees who have participated
                      in an old project.<br />
                      <br />
                      <img alt="" src="delt1.gif" /><br />
                      <br />
                      <br />
                      Open the data export dialog. Enter <span style=
                      "font-style: italic;">delete-old-projects.sql</span> as name for
                      the delete-script and start the exportation.<br />

                      <p><img src="deleting-data-Dateien/screen17.png" /><br /></p>

                      <p><img alt="" src="delt2.gif" /><br /></p>

                      <p><br />
                      Jailer has generated a script for the deletion of the two old
                      projects and the six associated participations, but also for the
                      deletion of six employees and one role. This role is the role of an
                      participation in an old project, but not of a remaining one. The
                      six employees have participated in an old project but in none of
                      the remaining projects.<br />
                      <br /></p>

                      <h4>Step 2. Prevent employees and roles from being
                      deleted<br /></h4>Open the data model editor (<span style=
                      "font-style: italic;">Tools-&gt;Data Model Editor</span>). Edit the
                      table EMPLOYEE and switch on the check-box "<span style=
                      "font-style: italic;">exclude from deletion</span>". Do the same
                      with table ROLE.<br />
                      (Since version 6.7 it's also possible to do this via the table's
                      context menu)<br />
                      <br />
                      <img alt="" src="delt3.gif" style=
                      "width: 600px; height: 350px;" /><br />

                      <p><br />
                      <span style="font-style: italic;">Export Data</span> again.
                      Employees and roles are now excluded from deletion:<br />
                      <br /></p>

                      <table style=
                      "background-color: rgb(51, 102, 255); text-align: left;" border="0"
                      cellpadding="2" cellspacing="0">
                        <tbody>
                          <tr>
                            <td style=
                            "vertical-align: top; height: 24px; background-color: rgb(202, 225, 235);">
                            <big><span style="font-family: monospace;"><span style=
                            "font-weight: bold;"><span style=
                            "font-family: monospace;">delete-old-projects.sql</span></span></span>
                            </big><br /></td>
                          </tr>
                        </tbody>
                      </table>

                      <table style="width: 100%; text-align: left;" border="0"
                      cellpadding="0" cellspacing="0">
                        <tbody>
                          <tr>
                            <td style=
                            "vertical-align: top; background-color: rgb(202, 225, 235);"
                            colspan="2"><span style="font-family: monospace;">--
                            generated by Jailer at Wed Fri May 02 12:37:07 CEST
                            2016&nbsp; from wisser@desktop<br />
                            --<br />
                            -- extraction model:&nbsp; EMPLOYEE where NAME='SCOTT'
                            (extractionmodel/scott.csv)<br />
                            -- database URL:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                            jdbc:db2://localhost/wisser<br />
                            -- database user:&nbsp;&nbsp;&nbsp;&nbsp; scott<br />
                            -- Exported Entities: 25<br />
                            --&nbsp;&nbsp;&nbsp;&nbsp;
                            DEPARTMENT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                            2<br />
                            --&nbsp;&nbsp;&nbsp;&nbsp;
                            EMPLOYEE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                            8<br />
                            --&nbsp;&nbsp;&nbsp;&nbsp;
                            PROJECT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                            2<br />
                            --&nbsp;&nbsp;&nbsp;&nbsp;
                            PROJECT_PARTICIPATION&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                            6<br />
                            --&nbsp;&nbsp;&nbsp;&nbsp;
                            ROLE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                            3<br />
                            --&nbsp;&nbsp;&nbsp;&nbsp;
                            SALARYGRADE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                            4<br />
                            --<br />
                            -- Tabu-tables:&nbsp; { EMPLOYEE, ROLE }<br />
                            --<br />
                            -- Deleted Entities: 8<br />
                            --&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                            DEPARTMENT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0
                            (-2)<br />
                            --&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                            PROJECT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                            2<br />
                            --&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                            PROJECT_PARTICIPATION&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                            6<br />
                            --&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SALARYGRADE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0
                            (-4)<br />
                            <br />
                            <br />
                            Delete from PROJECT_PARTICIPATION Where (PROJECTNO, EMPNO,
                            START_DATE) in (values (1001, 7369, '2006-01-01'), (1001,
                            7788, '2006-05-15'), (1001, 7902, '2006-01-01'), (1002, 7782,
                            '2006-08-22'), (1002, 7876, '2006-08-22'), (1002, 7934,
                            '2007-01-01'));<br />
                            Delete from PROJECT Where PROJECTNO in (1001, 1002);<br />
                            <br /></span></td>
                          </tr>
                        </tbody>
                      </table><br />
                      <br />

                      <p><br />
                      <br /></p>
                    </td>
                  </tr>
                </tbody>
              </table>
            </div>

            <p>&nbsp;</p>
          </td>
        </tr>

        <tr>
          <td height="12" valign="top" width="14%"><br /></td>

          <td><br /></td>
        </tr>
      </tbody>
    </table>
  </div>
</body>
</html>
